﻿Imports System.Data.SqlClient
Imports System.Data
Public Class ChuNhaDAO
    Public Function CheckEmail(ByVal tk As ChuNhaDTO) As Boolean
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "usp_KTTonTaiEmailChuNha"
            cmd.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter = cmd.Parameters.Add("@Email", SqlDbType.VarChar)
            para.Value = tk.Email

            Dim a As SqlParameter = New SqlParameter("@flag", SqlDbType.Int)
            a.Direction = ParameterDirection.Output
            cmd.Parameters.Add(a)

            cmd.ExecuteScalar()

            Dim flag As Integer = cmd.Parameters("@flag").Value
            If flag = 1 Then
                Return True
            End If
            Return False
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return True
        End Try
        Return False
    End Function
    Public Sub GetSVTK(ByVal tenDN As String, ByRef cn As ChuNhaDTO, ByRef tk As TaiKhoanDTO)
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select tk.MaTaiKhoan,tk.TenTaiKhoan,tk.MatKhau,tk.MaLoai,tk.TrangThai,cn.MaChuNha,cn.HoTen,cn.DienThoai,cn.CMND,cn.Email from CHUNHA cn, TAIKHOAN tk where tk.MaTaiKhoan=cn.MaTaiKhoan and tk.TenTaiKhoan='" + tenDN + "'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        da.Fill(data)
        Try
            cn.MaChuNha = Convert.ToInt32(data.Rows(0)("MaChuNha").ToString())
            cn.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            cn.HoTen = data.Rows(0)("HoTen").ToString()
            cn.Email = data.Rows(0)("Email").ToString()
            cn.DienThoai = data.Rows(0)("DienThoai").ToString()
            cn.CMND = data.Rows(0)("CMND").ToString()

            tk.MaLoai = Convert.ToInt32(data.Rows(0)("MaLoai").ToString())
            tk.TenTaiKhoan = data.Rows(0)("TenTaiKhoan").ToString()
            tk.MatKhau = data.Rows(0)("MatKhau").ToString()
            tk.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            tk.TrangThai = Convert.ToInt32(data.Rows(0)("TrangThai").ToString())
        Catch ex As Exception

        End Try
    End Sub
    Public Function GetItemID(ByVal tenDN As String) As ChuNhaDTO
        Dim chunha As New ChuNhaDTO
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select cn.MaChuNha,cn.MaTaiKhoan,cn.HoTen,cn.CMND,cn.Email,cn.DienThoai from CHUNHA cn,TAIKHOAN tk where cn.MaTaiKhoan = tk.MaTaiKhoan and tk.TenTaiKhoan='" + tenDN + "'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
            chunha.MaChuNha = Convert.ToInt32(data.Rows(0)("MaChuNha").ToString())
            chunha.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            chunha.HoTen = data.Rows(0)("HoTen").ToString()
            chunha.Email = data.Rows(0)("Email").ToString()
            chunha.CMND = data.Rows(0)("CMND").ToString()
            chunha.DienThoai = data.Rows(0)("DienThoai").ToString()
        Catch ex As Exception

        End Try
        Return chunha
    End Function
    Public Function GetItemMaChuNha(ByVal machunha As Integer) As ChuNhaDTO
        Dim chunha As New ChuNhaDTO
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from CHUNHA where MaChuNha=" + machunha.ToString()
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        da.Fill(data)
        Try
            chunha.MaChuNha = Convert.ToInt32(data.Rows(0)("MaChuNha").ToString())
            chunha.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            chunha.HoTen = data.Rows(0)("HoTen").ToString()
            chunha.Email = data.Rows(0)("Email").ToString()
            chunha.DienThoai = data.Rows(0)("DienThoai").ToString()
        Catch ex As Exception

        End Try
        Return chunha
    End Function
    Public Function GetAll() As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from CHUNHA"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function Insert(ByVal chunha As ChuNhaDTO) As Boolean
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_InsertCHUNHA @MaTaiKhoan,@HoTen,@DienThoai,@CMND,@Email,@MaChuNha"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaTaiKhoan", SqlDbType.Int)
            para.Value = chunha.MaTaiKhoan
            para = cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar)
            para.Value = chunha.HoTen
            para = cmd.Parameters.Add("@DienThoai", SqlDbType.VarChar)
            para.Value = chunha.DienThoai
            para = cmd.Parameters.Add("@CMND", SqlDbType.VarChar)
            para.Value = chunha.CMND
            para = cmd.Parameters.Add("@Email", SqlDbType.VarChar)
            para.Value = chunha.Email
            para = cmd.Parameters.Add("@MaChuNha", SqlDbType.Int)
            para.Value = chunha.MaChuNha
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
        Return False
    End Function
    Public Sub Delete(ByVal chunha As ChuNhaDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_DeleteCHUNHA @MaChuNha"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaChuNha", SqlDbType.Int)
            para.Value = chunha.MaChuNha
            cmd.ExecuteNonQuery()
            MessageBox.Show("Chủ nhà đã được xóa thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Sub Update(ByVal chunha As ChuNhaDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_UpdateCHUNHA @MaChuNha,@HoTen,@DienThoai,@Email,@CMND"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaChuNha", SqlDbType.Int)
            para.Value = chunha.MaChuNha
            para = cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar)
            para.Value = chunha.HoTen
            para = cmd.Parameters.Add("@DienThoai", SqlDbType.VarChar)
            para.Value = chunha.DienThoai
            para = cmd.Parameters.Add("@Email", SqlDbType.VarChar)
            para.Value = chunha.Email
            para = cmd.Parameters.Add("@CMND", SqlDbType.VarChar)
            para.Value = chunha.CMND
            cmd.ExecuteNonQuery()
            MessageBox.Show("Chủ nhà đã cập nhật tạo thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class
